package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.ConfigProperties;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorExecpuDetailService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("monitorExecpuDetailService")
public class MonitorExecpuDetailService implements IMonitorExecpuDetailService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");
        final String recordName = "etcboutlist_monitor_detail";

        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
            if (recordStr == null) {
                throw new Exception("[异常]本次最大记录RECORDID为空");
            }
            XxlJobLogger.log("[普通]本次最大记录RECORDID为:" + recordStr);
            String dbCurTime = DBUtils.getDBCurTime(connPre);
            XxlJobLogger.log("[普通]本次同步截止时间为:" + dbCurTime);

            //同步出口数据到临时表
            syncExecpuRecord(connPre, recordStr, dbCurTime);
            //同步异常数据到临时表
//            syncExceptionRecord(connPre, recordStr, dbCurTime);
            //拆包跨省流水给部里
            analyzePackageCenter(connPre);
            //拆包本省流水给发行方
            analyzePackageIssue(connPre);
            //拆包部里返回的记账数据
            analyzePackageCenterAcc(connPre);
            //拆包发行方返回的记账数据
            analyzePackageIssueAcc(connPre);
            //拆分统计

            Integer count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常]更新记录时间异常");
                throw new Exception("[异常]更新记录时间异常");
            }
            XxlJobLogger.log("[普通]更新记录时间为:" + dbCurTime);

            //删除7天前历史数据
            deleteOldData(connPre, dbCurTime);

            connPre.commit();

            String sqlPro = "truncate table TB_SYNC_MONITOR_ETC_TMP";
            DBUtils.execute(connPre, sqlPro);
            sqlPro = "truncate table TB_SYNC_MONITOR_ETC_ERR_TMP";
            DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]重置临时表成功");

            connPre.commit();

            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    void syncExecpuRecord(Connection conn, String recordStr, String dbCurTime) throws Exception {

        String sql = "insert into TB_SYNC_MONITOR_ETC_TMP(LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,ELEPAYMETHOD,VEHPLATE,QFSEND_STATE) " +
                "select ListNo,to_char(opTime,'yyyy-mm-dd'),vehclass,signStatus,ELEPAYMETHOD,VEHPLATE,1 " +
                "from ETCBOutList b " +
                "where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
        Integer count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次从中台系统共同步正常数据(条):" + count);

        sql = "MERGE INTO TB_SYNC_MONITOR_ETC_TMP a\n" +
                "USING (\n" +
                "  select ListNo,opTime,vehclass,signStatus,ELEPAYMETHOD,VEHPLATE \n" +
                "  from ETCBOutList_all where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') " +
                ") b \n" +
                "ON (a.LIST_NO=b.listno) \n" +
                "WHEN NOT MATCHED THEN \n" +
                "  insert (LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,ELEPAYMETHOD,VEHPLATE,QFSEND_STATE) \n" +
                "  values (b.ListNo,to_char(b.opTime,'yyyy-mm-dd'),b.vehclass,b.signStatus,b.ELEPAYMETHOD,b.VEHPLATE,0) ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次从中台系统共同步ALL表数据(条):" + count);

        sql = "MERGE INTO TB_SYNC_MONITOR_EXETCPU_0 a " +
                "USING TB_SYNC_MONITOR_ETC_TMP b " +
                "ON (a.LIST_NO=b.LIST_NO) " +
                "WHEN MATCHED THEN " +
                "  UPDATE SET a.RECV_STATE=1,a.QFSEND_STATE=b.QFSEND_STATE,a.UPDATE_TIME=sysdate," +
                "  a.NOCLEAR_STATE=(case when b.VEH_CLASS in (9,14,39) or b.SIGN_STATUS!=1 or b.ELEPAYMETHOD in (7,8,107,108) or b.VEHPLATE like '闯关%' then 0 else 1 end) " +
                "WHEN NOT MATCHED THEN " +
                "  INSERT (a.LIST_NO,a.TRADE_DATE,a.RECV_STATE,a.QFSEND_STATE,a.NOCLEAR_STATE,a.CLEAR_STATE,a.ACC_STATE,a.SPLIT_STATE,a.INSERT_TIME,a.UPDATE_TIME) " +
                "  VALUES (b.LIST_NO,b.TRADE_DATE,1,b.QFSEND_STATE,(case when b.VEH_CLASS in (9,14,39) or b.SIGN_STATUS!=1 or b.ELEPAYMETHOD in (7,8,107,108) or b.VEHPLATE like '闯关%' then 0 else 1 end),0,0,0,sysdate,sysdate) ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次合并正常ETC出口流水到监视表(条):" + count);

        sql = "insert into TB_SYNC_MONITOR_ETC_BAK(LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,INSERT_TIME) " +
                "select LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,sysdate from TB_SYNC_MONITOR_ETC_TMP ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次备份正常ETC出口流水(条):" + count);

        sql = "delete from TB_SYNC_MONITOR_ETC_TMP ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次清空正常ETC出口临时流水(条):" + count);
    }

    void syncExceptionRecord(Connection conn, String recordStr, String dbCurTime) throws Exception {
        String sql = "insert into TB_SYNC_MONITOR_ETC_ERR_TMP(LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS) " +
                "select ListNo,to_char(opTime,'yyyy-mm-dd'),vehclass,signStatus " +
                "from ETCBOutList_err b " +
                "where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
        Integer count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次从中台系统共同步异常数据(条):" + count);

        sql = "MERGE INTO TB_SYNC_MONITOR_EXETCPU_0 a " +
                "USING TB_SYNC_MONITOR_ETC_ERR_TMP b " +
                "ON (a.LIST_NO=b.LIST_NO) " +
//                "WHEN MATCHED THEN " +
//                "  UPDATE SET a.RECV_STATE=0,a.UPDATE_TIME=sysdate," +
//                "  a.NOCLEAR_STATE=(case when b.VEH_CLASS in (9,14,39) or b.SIGN_STATUS!=1 or b.ELEPAYMETHOD in (7,8,107,108) or b.VEHPLATE like '闯关%' then 0 else 1 end) " +
                "WHEN NOT MATCHED THEN " +
                "  INSERT (a.LIST_NO,a.TRADE_DATE,a.RECV_STATE,a.QFSEND_STATE,a.NOCLEAR_STATE,a.CLEAR_STATE,a.ACC_STATE,a.SPLIT_STATE,a.INSERT_TIME,a.UPDATE_TIME) " +
                "  VALUES (b.LIST_NO,b.TRADE_DATE,0,0,(case when b.VEH_CLASS in (9,14,39) or b.SIGN_STATUS!=1 or b.ELEPAYMETHOD in (7,8,107,108) or b.VEHPLATE like '闯关%' then 0 else 1 end),0,0,0,sysdate,sysdate) ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次合并异常ETC出口流水到监视表(条):" + count);

        sql = "insert into TB_SYNC_MONITOR_ETC_ERR_BAK(LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,INSERT_TIME) " +
                "select LIST_NO,TRADE_DATE,VEH_CLASS,SIGN_STATUS,sysdate from TB_SYNC_MONITOR_ETC_ERR_TMP ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次备份异常ETC出口流水(条):" + count);

        sql = "delete from TB_SYNC_MONITOR_ETC_ERR_TMP ";
        count = DBUtils.execute(conn, sql);
        XxlJobLogger.log("[普通]本次清空异常ETC出口临时流水(条):" + count);
    }

    void analyzePackageCenter(Connection conn) throws Exception {

    }

    void analyzePackageIssue(Connection conn) throws Exception {

    }

    void analyzePackageCenterAcc(Connection conn) throws Exception {

    }

    void analyzePackageIssueAcc(Connection conn) throws Exception {

    }

    void deleteOldData(Connection conn, String dbCurTime) throws Exception {
        String sqlPro = "";
        //清除7天前的数据（配置文件中配置）
        String strHour = dbCurTime.substring(11,13);
        if (strHour.equals("02")) { //凌晨2点执行一次删除7日前的数据
            String strCurDay = dbCurTime.substring(0,10);
            final String recordDel = "del_tb_sync_monitor_exetcpu_0";
            String recordStrDel = DBUtils.getCurSynRecordNum(connPre, recordDel);
            XxlJobLogger.log("[普通]本次定时清除历史数据RECORDID为:" + recordStrDel);
            if (recordStrDel != null && strCurDay.compareTo(recordStrDel) > 0) {

                ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
                String dataDays = configProperties.getDataDays();

                sqlPro = "delete from TB_SYNC_MONITOR_EXETCPU_0 where TRADE_DATE<to_char(sysdate-interval'"+dataDays+"'day, 'yyyy-mm-dd') ";
                Integer count = DBUtils.execute(connPre, sqlPro);
                XxlJobLogger.log("[普通]本次删除异常明细表"+dataDays+"天前数据(条):"+count);

                count = DBUtils.modifySynRecordNum(connPre, recordDel, recordStrDel, strCurDay);
                if (count == null || count != 1) {
                    XxlJobLogger.log("[异常]更新"+dataDays+"日前数据清除日期异常");
                    throw new Exception("[异常]更新"+dataDays+"日前数据清除日期异常");
                }
                XxlJobLogger.log("[普通]更新"+dataDays+"日前数据清除日期为:" + strCurDay);

                //7天凌晨执行一次重新分析统计信息
                Integer nDay = Integer.parseInt(dbCurTime.substring(8,10));
                if (nDay % 7 == 1) {
                    sqlPro = "analyze table TB_SYNC_MONITOR_EXETCPU_0 compute statistics ";
                    count = DBUtils.execute(connPre, sqlPro);
                    XxlJobLogger.log("[普通]异常明细表重新分析统计信息(条):"+count);
                }
            }
        }
    }
}
